*This file uses an internal CFPB database database collecting consumer complaints on a range of consumer financial products and services, including GPRs.
*This do file walks a user through the process of replicating Figure 9, which is uses this database, should one gain access to the database.




cls
set more off
set matsize 2000
capture log close

global directory "/home/work/users/you/updated_data"
global today : display %tdCYND date(c(current_date), "DMY")

global output "$directory/output"
cd "$directory/programs"




**
**
* Importing and appending csv files

clear 
cd "$directory/data"

* Un-comment if re-importing from csv

* Get a list of all the relevant files in your directory
local flist: dir "." files "*.csv"  

* Import each file in the list and save it to a tempfile
local nfile 0
foreach fname of local flist {
    local ++nfile     
    import delimited "`fname'", delimiter(comma) varnames(1) bindquote(strict) clear   
    tempfile temp`nfile'
    save "`temp`nfile''"
}    
* Append all of the nfile files
clear
forval i = 1/`nfile' {
  append using "`temp`i''" , force 
}

compress
save combined.dta, replace


* uncomment if importing from stata file
*use combined 




**
**
****************************
* Data manipulation
****************************

* Remove PII
drop consumername consumerphonenumber emailaddress location servicemember olderamerican


****************************
* Dates
****************************
* Date
gen dated = date(datereceived, "YMD") 
format dated %td

* Yearly
gen datey = yofd(dated) 
format datey %ty

* Monthly
gen datem = mofd(dated) 
format datem %tm

* Weekly
gen datew = wofd(dated) 
format datew %tw

gen month = month(dated)
gen year  = year(dated)





gen consumerco=lower(consumerenteredcompany)
*to standardize capitalization

*****
*****Chase Liquid
gen chaseliquid = strpos(consumerco, "chase") > 0
tab datem chaseliquid


******
******Halogen Reloadable Prepaid Card/Mastercard, Kmart, Green Dot Bank
gen halogen = strpos(consumerco, "halogen") > 0
tab datem halogen

******
******Bluebird
gen bluebird = strpos(consumerco, "bluebird") > 0
replace bluebird=1 if strpos(consumerco, "blue bird")
tab datem bluebird

********
********American Express Serve
gen amexpserve = (strpos(consumerco, "american") & strpos(consumerco, "express") & strpos(consumerco, "serve")) > 0
tab datem amexpserve


******
******Fifth Third Bank Access 360/Mastercard
gen fifththirdbank = (strpos(consumerco, "fifth") & strpos(consumerco, "third")) > 0
replace fifththirdbank=1 if strpos(consumerco, "5th")
tab datem fifththirdbank

******
******BB&T MoneyAccount Card/Visa
gen bbt = strpos(consumerco, "bb&t") > 0
replace bbt=1 if strpos(consumerco, "bb and t")
tab datem bbt


******
*******US Bank Contour Card/Visa
gen usbankcontour = (strpos(consumerco, "us bank")) > 0
replace usbankcontour=1 if strpos(consumerco, "usbank")
tab datem usbankcontour

*****
*****Prepaid Visa RushCard, Pay As You Go Plan/Metabank
gen rushpayasgo = (strpos(consumerco, "rush") & strpos(consumerco, "card"))> 0
tab datem rushpayasgo

*****
*****Regions Now Card/Visa
gen regionsnow = (strpos(consumerco, "regions"))> 0
tab datem regionsnow

*****
*****H&R Block Emerald Prepaid MasterCard/Bofl Federal Bank
gen hrblockemerald = (strpos(consumerco, "block"))> 0
tab datem hrblockemerald

*****
*****Walmart MoneyCard Mastercard/Green Dot Bank
gen walmartmoneycard = (strpos(consumerco, "walmart") & strpos(consumerco, "money"))> 0
replace walmartmoneycard=1 if strpos(consumerco, "walmart") & strpos(consumerco, "green")
replace walmartmoneycard=1 if strpos(consumerco, "walmart") & strpos(consumerco, "master")
tab datem walmartmoneycard

******
******Green Dot Prepaid Visa
gen greendot = (strpos(consumerco, "green") & strpos(consumerco, "dot")) > 0
replace greendot=0 if walmartmoneycard==1
tab datem greendot

*****
***** PNC SmartAccess Prepaid Visa Card
gen pncsmartaccess = (strpos(consumerco, "pnc"))> 0
tab datem pncsmartaccess
tab datem pncsmartaccess

*****
*****Upside Visa Prepaid Card, Classic Plan/Metabank
gen upside = (strpos(consumerco, "upside"))> 0
tab datem upside

*****
***** Paypower Prepaid Visa Prepaid Card/Metabank
gen paypower = (strpos(consumerco, "pay") & strpos(consumerco, "power"))> 0
tab datem paypower

*****
*****Univsion Mastercard Prepaid Card/Metabank
gen univision = (strpos(consumerco, "univision"))> 0
tab datem univision

*****
*****AccountNow Gold Visa Prepaid Card/MetaBank
gen accountnowgold = (strpos(consumerco, "account") & strpos(consumerco, "now") & strpos(consumerco, "gold"))> 0
replace accountnowgold=1 if strpos(consumerco, "account") & strpos(consumerco, "now")
replace accountnowgold=1 if strpos(consumerco, "accoun") & strpos(consumerco, "now")
tab datem accountnowgold

*****
***** NetSpend
gen netspend = (strpos(consumerco, "net") & strpos(consumerco, "spend")) > 0
tab datem netspend

*** additional Cards found in the 2014 CR to give us slightly larger sampple of the total complaints for whom we can categorize the clarity type
gen mango = strpos(consumerco, "mango") > 0
gen target = strpos(consumerco, "target") > 0
gen walgreen = strpos(consumerco, "walgreen") > 0



gen unclear=.
replace unclear=1 if halogen==1 | walmartmoneycard==1 | pncsmartaccess==1 | accountnowgold==1 | netspend==1 | walgreen==1 | target==1 | mango==1
replace unclear=0 if chaseliquid==1 | amexpserve==1 | bluebird==1 | fifththirdbank==1 | bbt==1 | usbankcontour==1 | rushpayasgo==1 | regionsnow==1 | hrblockemerald==1 | upside==1 | paypower==1 | univision==1
* Final card counts of each type: 7 unclear=1 cards and 10 unclear=0 cards (Halogen, upside, paypower have no complaints on or after Nov18 and thus are not relevant in our time period, so including them in these lines (or not) changes nothing below; Greendot is removed in order to create control group whose pre-trends that match more closely)




* COMPUTING THE MONTHLY AVG COMPLAINT COUNT BY CLARITY TYPE *

*** Clarity by Month ***
* Reduce domain
* dofm(november,2018) == 21489
keep if dofm(datem)>=21489 & dofm(datem)<=21670
contract datem unclear, freq(count_) zero

gen count_unclear=count_ if unclear==1
gen count_clear=count_ if unclear==0


* deflate by number of cards
*scalar cardnum_unclear = 8 
*scalar cardnum_clear = 12
scalar cardnum_unclear = 7 
scalar cardnum_clear = 10
gen deflated_unclear=count_unclear/cardnum_unclear
gen deflated_clear=count_clear/cardnum_clear


* graph and save
twoway (connected deflated_unclear datem) (connected deflated_clear datem, msymbol(Sh)), legend(order(1 "Less Clarity" 2 "More Clarity")) xline(4) xtitle("") ytitle("Average Number of Complaints (per card)") xlabel(#6, format(%tmMonYY))
graph save Graph $output/Figure9.gph, replace
graph export $output/Figure9.png, as(png) replace

